package unilearn.da;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Vector;

import javax.naming.NamingException;

import unilearn.domain.Submission;

/**
 * @author Ben Morall, 4158768
 * @author Yin Zhang
 * @version 0.1, April 24 2008, inital creation, blm
 * @version 0.2, May 07 2008, add assigned_to field to database
 * @version 0.3, 14 May 2008, Code Refactor, blm
 * @version 0.4, 14 May 2008, Get Methods now return Vector, blm
 */
public class DASubmission extends DABase
{
	private static final String SQL_ASSIGN_SUBMISSION_TO = "SELECT s.* FROM submission s, Assignment a, Subject j"
			+ " WHERE a.assignment_id = s.assignment_id AND j.subject_id = a.subject_id AND s.assigned_to = ? ORDER BY j.subject_code, a.assignment_name, s.assigned_to";
	private static final String SQL_DELETE_SUBMISSION = "DELETE FROM submission WHERE submission_id = ?";
	private static final String SQL_GET_SUBMISSION = "SELECT * FROM submission WHERE submission_id = ?";
	private static final String SQL_GET_STUDENT_SUBMISSION = "SELECT s.* FROM submission s, Assignment a, Subject j"
			+ " WHERE a.assignment_id = s.assignment_id AND j.subject_id = a.subject_id AND s.student_number = ? ORDER BY j.subject_code, a.assignment_name, s.student_number";
	private static final String SQL_GET_STUDENT_NUMBERS = "SELECT student_number FROM submission GROUP BY student_number ORDER BY student_number";
	private static final String SQL_GET_ASSESSMENT_SUBMISSION = "SELECT s.* FROM submission s, assessment a WHERE a.submission_id = s.submission_id AND a.assessment_id = ?";
	private static final String SQL_GET_ASSIGNMENT_SUBMISSIONS = "SELECT * FROM submission WHERE assignment_id = ?";
	private static final String SQL_INSERT_SUBMISSION = "INSERT INTO submission (assignment_id, date_submitted, student_number, file_id) VALUES (?,?,?,?)";
	private static final String SQL_INSERT_SUBMISSION_HORROR = "INSERT INTO submission (assignment_id, date_submitted, student_number, file_id, assigned_to) VALUES (?,?,?,?,?)";
	private static final String SQL_UPDATE_SUBMISSION = "UPDATE submission SET date_submitted = ?, file_id = ? WHERE submission_id = ?";

	public static boolean deleteSubmission(long submissionID) throws SQLException,
		NamingException
	{
		Connection conn;
		PreparedStatement stmt;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_DELETE_SUBMISSION);
		try
		{
			stmt.setLong(1, submissionID);
			return stmt.execute();
		}
		finally
		{
			stmt.close();
		}
	}

	/**
	 * Generates a Submission from statements in a ResultSet
	 * @param rs ResultSet containing a Submission
	 * @return a Submission from <code>rs</code>
	 */
	private static Submission generateSubmission(ResultSet rs) throws SQLException
	{
		Submission sub = new Submission(rs.getLong("submission_id"));
		sub.setAssignmentID(rs.getLong("assignment_id"));
		sub.setDateSubmitted(rs.getTimestamp("date_submitted"));
		sub.setFileID(rs.getLong("file_id"));
		sub.setStudentNumber(rs.getString("student_number"));
		return sub;
	}

	/**
	 * Testing Method Only
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 * @deprecated
	 */
	@Deprecated
	public static String[] getStudentNumbers() throws SQLException, NamingException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Vector<String> subList;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_STUDENT_NUMBERS);
		try
		{
			/* Generate ResultSet */
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new Vector<String>();
			while (rs.next())
			{
				subList.add(rs.getString("student_number"));
			}
			return subList.toArray(new String[0]);
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Submission getSubmission(long submissionID) throws SQLException,
		NamingException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setLong(1, submissionID);
			rs = stmt.executeQuery();

			/* Return Record */
			if (!rs.next())
			{
				/* No Records Found */
				return null;
			}
			else
			{
				return generateSubmission(rs);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Submission getSubmissionByAssessment(long assessmentID)
		throws SQLException, NamingException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_ASSESSMENT_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setLong(1, assessmentID);
			rs = stmt.executeQuery();

			/* Return Record */
			if (!rs.next())
			{
				/* No Records Found */
				return null;
			}
			else
			{
				return generateSubmission(rs);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Vector<Submission> getSubmissionsByAssignment(long assignmentID)
		throws SQLException, NamingException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Vector<Submission> subList;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_ASSIGNMENT_SUBMISSIONS);
		try
		{
			/* Generate ResultSet */
			stmt.setLong(1, assignmentID);
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new Vector<Submission>();
			while (rs.next())
			{
				subList.add(generateSubmission(rs));
			}
			return subList;
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Vector<Submission> getSubmissionsByStaff(String staffID)
		throws SQLException, NamingException
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<Submission> subList;

		Connection conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_ASSIGN_SUBMISSION_TO);
		try
		{
			/* Generate ResultSet */
			stmt.setString(1, staffID);
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new Vector<Submission>();
			while (rs.next())
			{
				subList.add(generateSubmission(rs));
			}
			return subList;
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Vector<Submission> getSubmissionsByStudent(String studentNumber)
		throws SQLException, NamingException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Vector<Submission> subList;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_STUDENT_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setString(1, studentNumber);
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new Vector<Submission>();
			while (rs.next())
			{
				subList.add(generateSubmission(rs));
			}
			return subList;
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	private static Timestamp getNow()
	{
		java.util.Date today = new java.util.Date();
		return new Timestamp(today.getTime());
	}

	@Deprecated
	public static Submission insertSubmissionBAD(Submission sub) throws SQLException,
		NamingException
	{
		if (sub == null) { throw new NullPointerException("studentNumber"); }
		if (sub.getAssignmentID() == -1)
			throw new IllegalArgumentException("Submission already inserterd");

		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Timestamp date = getNow();

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_INSERT_SUBMISSION_HORROR,
				Statement.RETURN_GENERATED_KEYS);
		try
		{
			/* Generate Submission */
			stmt.setLong(1, sub.getAssignmentID());
			stmt.setTimestamp(2, date);
			stmt.setString(3, sub.getStudentNumber());
			stmt.setLong(4, sub.getFileID());
			stmt.setString(5, sub.getStaffID());
			stmt.execute();

			/* Get ID */
			rs = stmt.getGeneratedKeys();
			if (rs == null) { throw new NullPointerException("rs"); }

			/* Return Record */
			if (rs.next())
			{
				sub.setSubmissionID(rs.getLong(1));
			}
			else
			{
				throw new RuntimeException("Submission not created");
			}

			return sub;
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Submission insertSubmission(Submission sub) throws SQLException,
		NamingException
	{
		if (sub == null) { throw new NullPointerException("studentNumber"); }
		if (sub.getAssignmentID() == -1)
			throw new IllegalArgumentException("Submission already inserterd");

		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Timestamp date = getNow();

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_INSERT_SUBMISSION, Statement.RETURN_GENERATED_KEYS);
		try
		{
			/* Generate Submission */
			stmt.setLong(1, sub.getAssignmentID());
			stmt.setTimestamp(2, date);
			stmt.setString(3, sub.getStudentNumber());
			stmt.setLong(4, sub.getFileID());
			stmt.execute();

			/* Get ID */
			rs = stmt.getGeneratedKeys();
			if (rs == null) { throw new NullPointerException("rs"); }

			/* Return Record */
			if (rs.next())
			{
				sub.setSubmissionID(rs.getLong(1));
			}
			else
			{
				throw new RuntimeException("Submission not created");
			}

			return sub;
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static void updateSubmission(long submissionID, long fileID) throws SQLException,
		NamingException
	{
		Connection conn;
		PreparedStatement stmt;
		Timestamp date = getNow();

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_UPDATE_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setTimestamp(1, date);
			stmt.setLong(2, fileID);
			stmt.setLong(3, submissionID);
			stmt.execute();
		}
		finally
		{
			stmt.close();
		}
	}
}
